package com.bookworld.dao;

import com.bookworld.model.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface UserDao {
    
    @Select("SELECT * FROM users WHERE id = #{id}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "password", column = "password"),
        @Result(property = "firstName", column = "first_name"),
        @Result(property = "lastName", column = "last_name"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "dateOfBirth", column = "date_of_birth"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "address", column = "address"),
        @Result(property = "city", column = "city"),
        @Result(property = "country", column = "country"),
        @Result(property = "postalCode", column = "postal_code"),
        @Result(property = "balance", column = "balance"),
        @Result(property = "avatar", column = "avatar"),
        @Result(property = "active", column = "active"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    User findById(Long id);
    
    @Select("SELECT * FROM users WHERE username = #{username}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "password", column = "password"),
        @Result(property = "firstName", column = "first_name"),
        @Result(property = "lastName", column = "last_name"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "dateOfBirth", column = "date_of_birth"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "address", column = "address"),
        @Result(property = "city", column = "city"),
        @Result(property = "country", column = "country"),
        @Result(property = "postalCode", column = "postal_code"),
        @Result(property = "balance", column = "balance"),
        @Result(property = "avatar", column = "avatar"),
        @Result(property = "active", column = "active"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    User findByUsername(String username);
    
    @Select("SELECT * FROM users WHERE email = #{email}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "password", column = "password"),
        @Result(property = "firstName", column = "first_name"),
        @Result(property = "lastName", column = "last_name"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "dateOfBirth", column = "date_of_birth"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "address", column = "address"),
        @Result(property = "city", column = "city"),
        @Result(property = "country", column = "country"),
        @Result(property = "postalCode", column = "postal_code"),
        @Result(property = "balance", column = "balance"),
        @Result(property = "avatar", column = "avatar"),
        @Result(property = "active", column = "active"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    User findByEmail(String email);
    
    @Select("SELECT * FROM users WHERE username = #{username} OR email = #{email}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "password", column = "password"),
        @Result(property = "firstName", column = "first_name"),
        @Result(property = "lastName", column = "last_name"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "dateOfBirth", column = "date_of_birth"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "address", column = "address"),
        @Result(property = "city", column = "city"),
        @Result(property = "country", column = "country"),
        @Result(property = "postalCode", column = "postal_code"),
        @Result(property = "balance", column = "balance"),
        @Result(property = "avatar", column = "avatar"),
        @Result(property = "active", column = "active"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    User findByUsernameOrEmail(String username, String email);
    
    @Select("SELECT * FROM users WHERE active = #{active}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "password", column = "password"),
        @Result(property = "firstName", column = "first_name"),
        @Result(property = "lastName", column = "last_name"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "dateOfBirth", column = "date_of_birth"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "address", column = "address"),
        @Result(property = "city", column = "city"),
        @Result(property = "country", column = "country"),
        @Result(property = "postalCode", column = "postal_code"),
        @Result(property = "balance", column = "balance"),
        @Result(property = "avatar", column = "avatar"),
        @Result(property = "active", column = "active"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<User> findByActive(Boolean active);
    
    @Select("SELECT * FROM users ORDER BY created_at DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "password", column = "password"),
        @Result(property = "firstName", column = "first_name"),
        @Result(property = "lastName", column = "last_name"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "dateOfBirth", column = "date_of_birth"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "address", column = "address"),
        @Result(property = "city", column = "city"),
        @Result(property = "country", column = "country"),
        @Result(property = "postalCode", column = "postal_code"),
        @Result(property = "balance", column = "balance"),
        @Result(property = "avatar", column = "avatar"),
        @Result(property = "active", column = "active"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<User> findAllWithPagination(int limit, int offset);
    
    @Select("SELECT COUNT(*) FROM users")
    int count();
    
    @Insert("INSERT INTO users (username, email, password, first_name, last_name, phone, date_of_birth, gender, address, city, country, postal_code, balance, avatar, active, created_at, updated_at) " +
            "VALUES (#{username}, #{email}, #{password}, #{firstName}, #{lastName}, #{phone}, #{dateOfBirth}, #{gender}, #{address}, #{city}, #{country}, #{postalCode}, #{balance}, #{avatar}, #{active}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(User user);
    
    @Update("UPDATE users SET username=#{username}, email=#{email}, password=#{password}, first_name=#{firstName}, last_name=#{lastName}, " +
            "phone=#{phone}, date_of_birth=#{dateOfBirth}, gender=#{gender}, address=#{address}, city=#{city}, country=#{country}, " +
            "postal_code=#{postalCode}, balance=#{balance}, avatar=#{avatar}, active=#{active}, updated_at=#{updatedAt} WHERE id=#{id}")
    void update(User user);
    
    @Update("UPDATE users SET active=#{active} WHERE id=#{id}")
    void updateActiveStatus(@Param("id") Long id, @Param("active") Boolean active);
    
    @Update("UPDATE users SET balance=balance+#{amount} WHERE id=#{id}")
    void updateBalance(@Param("id") Long id, @Param("amount") BigDecimal amount);
    
    @Delete("DELETE FROM users WHERE id = #{id}")
    void delete(Long id);
    
    @Select("SELECT * FROM users WHERE username LIKE CONCAT('%', #{keyword}, '%') OR email LIKE CONCAT('%', #{keyword}, '%') OR first_name LIKE CONCAT('%', #{keyword}, '%') OR last_name LIKE CONCAT('%', #{keyword}, '%')")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "password", column = "password"),
        @Result(property = "firstName", column = "first_name"),
        @Result(property = "lastName", column = "last_name"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "dateOfBirth", column = "date_of_birth"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "address", column = "address"),
        @Result(property = "city", column = "city"),
        @Result(property = "country", column = "country"),
        @Result(property = "postalCode", column = "postal_code"),
        @Result(property = "balance", column = "balance"),
        @Result(property = "avatar", column = "avatar"),
        @Result(property = "active", column = "active"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<User> search(String keyword);
}
